#!/bin/bash
##################################################################
## function:hiveToMysql 折扣三期流量相关数据
## eg：bash track_z_kpi.sh 20150505
## author: shx
## createdate: 20150505 
## version:     
##################################################################
#import 
. /home/hadoop/shx/baseUtil.sh

HISTORYDATE=`date -d "1 day ago" +%Y%m%d`

if [[ $1 = 'mouth' ]] ; then
  datestr=`date  +%Y%m`"01"
  len='30'
elif [[ $1 = 'week' ]] ; then
  datestr=`date -d "$HISTORYDATE"`
  tst=`date -d "$HISTORYDATE" +%s`

  while [[ "`echo $datestr |awk '{print $4}'`" != "星期一" ]]
  do
          let "tst=tst-86400"
          echo $datestr
          datestr=`date -d "@$tst"`
  done
  datestr=`date -d "@$tst" +%Y%m%d`
  len='7'
elif [[ $1 = 'day' ]] ; then
  datestr=$HISTORYDATE
  len='1'
else
  datestr=$HISTORYDATE
  len='1'
fi

echo "the start day you wanted is =============="$datestr

#middle file path 
midPath="/home/hadoop/shx/zhekou3qi/midddata/middata_app_"
pt_midPath="/usr/local/cloud/src/plugins/data/product_type.txt"
resPath="/home/hadoop/shx/zhekou3qi/midddata/resdata_app_"



#产品类型 中间结果
echo `date`"==========qyer_lastminute lastminute_main 写入middata==============="  
mysql -u${MYSQL_183_USER} -p${MYSQL_183_PWD} -h${MYSQL_183_IP} -P${MYSQL_183_PORT} -e " use qyer_lastminute; select id , product_type from qyer_lastminute.lastminute_main " > $pt_midPath


#hive 中读取 流量数据写入middata
echo `date`"==========折扣三期的pv和uv 写入middata===============" 
/usr/local/cloud/hive/bin/hive -e  " 
use qyer ; 
add jar /home/hadoop/shx/zhekou3qi/job/ptbypid.jar;
CREATE TEMPORARY FUNCTION getProductType AS 'com.qyer.data.hive.udf.producttype.ProductTypeByPid';

select 
addtime,
fromtime , 
platform, 
channel , 
type , 
if (ugc_type like '%detail%' , 'detail' ,if (ugc_type like '%chose%' , 'chose' ,ugc_type )  ) as ugc_type , 
product_type , 
pv , 
uv , 
len
from  (

select 
unix_timestamp() as addtime , 
unix_timestamp (logdate , 'yyyyMMdd') as fromtime,
'app' as platform,
client_id as channel  , 
'1' as type , 
CASE regexp_extract(api_name , '_([a-z]+)$' ,1 ) 
        WHEN 'list' THEN 'chose'
        ELSE ''
        END  as ugc_type,
regexp_extract(info , 'product_type\"\;s:([0-9]+):\"([0-9]+)\"' , 2) as product_type, 
count(1) as pv , 
count(distinct (track_deviceid)) as uv , 
'$len' as len
from app_records 
where logdate>='$HISTORYDATE' and logdate<='$datestr' and 
(client_id='qyer_discount_ios' or client_id='qyer_discount_androi' or client_id='qyer_ios' or client_id='qyer_android' ) and 
( api_name like '/lastminute/get_lastminute_list' or api_name like '/lastminute/app_get_lastminute_list' or api_name like '/lastminute/app_lastminute_list' )  
group by 
logdate,
client_id,
regexp_extract(api_name , '_([a-z]+)$' ,1 ) ,
regexp_extract(info , 'product_type\"\;s:([0-9]+):\"([0-9]+)\"' , 2)

union all

select 
unix_timestamp() as addtime , 
unix_timestamp (a.logdate , 'yyyyMMdd') as fromtime,
'app' as platform,
a.platform as channel  , 
'1' as type , 
a.ugc_type  ,
getProductType(a.product_type)  as product_type,
count(1) as pv , 
count(distinct (track_deviceid)) as uv ,
'$len' as len
from (
select 
track_deviceid,
logdate ,
client_id as platform  , 
regexp_extract(api_name , '_([a-z]+)$' ,1 ) as ugc_type,
regexp_extract(api_path , '/([0-9]+)$' ,1 )  as product_type  
from app_records 
where logdate>='$HISTORYDATE' and logdate<='$datestr' and 
(client_id='qyer_discount_ios' or client_id='qyer_discount_androi' or client_id='qyer_ios' or client_id='qyer_android' ) and 
(api_name like '/lastminute/get_detail')  
) a 
group by 
a.logdate,
a.platform,
a.ugc_type,
getProductType(a.product_type) 

union all

select 
unix_timestamp() as addtime , 
unix_timestamp (logdate , 'yyyyMMdd') as fromtime,
'app' as platform,
client_id as channel  , 
'1' as type , 
'A'  as ugc_type,
'A' as product_type, 
count(1) as pv , 
count(distinct (track_deviceid)) as uv , 
'$len' as len
from app_records 
where logdate>='$HISTORYDATE' and logdate<='$datestr' and 
(client_id='qyer_discount_ios' or client_id='qyer_discount_androi' or client_id='qyer_ios' or client_id='qyer_android' )
group by 
logdate,
client_id

union all

select 
unix_timestamp() as addtime , 
unix_timestamp (logdate , 'yyyyMMdd') as fromtime,
'app' as platform,
client_id as channel  , 
'1' as type , 
CASE regexp_extract(api_name , '_([a-z]+)$' ,1 ) 
        WHEN 'list' THEN 'chose'
        WHEN 'detail' THEN 'detail'
        ELSE ''
END  as ugc_type,
'A' as product_type, 
count(1) as pv , 
count(distinct (track_deviceid)) as uv , 
'$len' as len
from app_records 
where logdate>='$HISTORYDATE' and logdate<='$datestr' and 
(client_id='qyer_discount_ios' or client_id='qyer_discount_androi' or client_id='qyer_ios' or client_id='qyer_android' ) and 
( api_name like '/lastminute/get_lastminute_list' or api_name like '/lastminute/app_get_lastminute_list' or api_name like '/lastminute/app_lastminute_list' or api_name like '/lastminute/get_detail' )  
group by 
logdate,
client_id,
regexp_extract(api_name , '_([a-z]+)$' ,1 )  

) b 
;
" > $midPath$HISTORYDATE




if [ $? -eq 0 ]
  then
  	echo `date`"=============== middle file is ok ===============" $midPath$HISTORYDATE

  	#load to database  
	 mysql -u${MYSQL_219_USER} -p${MYSQL_219_PWD} -h${MYSQL_219_IP} -P${MYSQL_219_PORT} -Dra -e " LOAD DATA LOCAL INFILE '$midPath$HISTORYDATE' INTO TABLE track_z_kpi_tmp FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'"
   echo "mysql -u${MYSQL_219_USER} -p${MYSQL_219_PWD} -h${MYSQL_219_IP} -P${MYSQL_219_PORT} -Dra -e \" LOAD DATA LOCAL INFILE '$midPath$HISTORYDATE' INTO TABLE track_z_kpi_tmp  FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'\""
  	if [ $? -eq 0 ]
  		then
  			echo `date`"================load data to database is ok !================" 
  		else
		  	echo `date`"================load data to database is error !================" 
		  	mailSend "147 :load data to database is error !"
	fi
else
  	echo `date`"================middle file is error !================" 
    mailSend "147 :middle file is error !"
fi








